<?php
namespace app\admin\controller;
use think\Loader;
use think\Controller;
use think\Db;
use think\Exception;
use think\Request;
class Excel extends CommController 
{
    public function excel_export () {
        return $this->fetch('imp_exp/excel_export');
    }

    //导出Excel
    function export(){
        // $a=Request::instance();
        // $stu_id=$a->post('stu_id');
        // // var_dump($stu_id);
        // $class=$a->post('class');
        // $dept=$a->post('dept');
        // $major=$a->post('major');
        // $grade=$a->post('grade');
        // var_dump($_POST);
        $stu_id=input('stu_id');
        $class=input('class');
        $dept=input('dept');
        $major=input('major');
        $grade=input('grade');
        if ($major=='请选择专业') {
            $major=null;
        }
        // var_dump($grade);
        $a='';
        $xlsName  = "export";
        $xlsCell  = array(
            array('stu_id','账号'),
            array('name','姓名'),
            array('class','班级'),
            array('dept','系别'),
            array('major','专业'),
            array('sex','性别'),
            array('grade','年级'),
            array('virture','美德学分'),
            array('wit','智学分'),
            array('sport','体得学分'),
            array('other_score','其他学分'),
            array('pro_score','项目学分'),
        );
        $sql="select * from user where (stu_id='".$stu_id."' or '".$stu_id."' = '".$a."' ) and (class='".$class."' or '".$class."' = '".$a."') and (dept= '".$dept."' or '".$dept."' = '".$a."') and (major = '".$major."' or '".$major."' =  '".$a."') and (grade = '".$grade."' or '".$grade."' = '".$a."')";
         $xlsData= Db::query($sql);
         var_dump($xlsData);
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
        // return $data;
    }
    function exportExcel($expTitle,$expCellName,$expTableData){
        include_once EXTEND_PATH.'PHPExcel/PHPExcel.php';//方法二
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $expTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        // $objPHPExcel = new PHPExcel();//方法一
        $objPHPExcel = new \PHPExcel();//方法二
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        // $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1',$expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        ob_end_clean();//这一步非常关键，用来清除缓冲区防止导出的excel乱码
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//"xls"参考下一条备注
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');//"Excel2007"生成2007版本的xlsx，"Excel5"生成2003版本的xls
        $objWriter->save('php://output');
        exit;
    }
    //导入
     public function savestudentImport(){  
        import('PHPExcel.PHPExcel', EXTEND_PATH);//方法二  
        // vendor("PHPExcel.PHPExcel"); //方法一  
        $objPHPExcel = new \PHPExcel();  
  
        //获取表单上传文件  
        $file = request()->file('excel'); 
        // var_dump( $file) ;
        if ($file!=null) 
        {
            $info = $file->validate(['ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel');  
            if($info){  
                $exclePath = $info->getSaveName();  //获取文件名  
                $file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $exclePath;   //上传文件的地址 
                // var_dump($file_name); 
                $objReader =\PHPExcel_IOFactory::createReader('Excel2007');  
                $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8  
                // var_dump($obj_PHPExcel);
                echo "<pre/>";  
                $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式  
                array_shift($excel_array);  //删除第一个数组(标题);  
                $city = [];  
                // var_dump($excel_array);
                $i=0;  
                if (sizeof($excel_array[0])!=8) {
                    echo "excel的数据列不匹配不对";
                }
                else
                {
                    foreach($excel_array as $k=>$v) {  
                        $city[$k]['stu_id'] = $v[0];
                        $city[$k]['name'] = $v[1];
                        $city[$k]['class'] = $v[2];
                        $city[$k]['dept'] = $v[3];
                        $city[$k]['major'] = $v[4];
                        $city[$k]['sex'] = $v[5];
                        $city[$k]['grade'] = $v[6];
                        $city[$k]['virture'] = $v[7];
                        $city[$k]['wit'] = $v[8];
                        $city[$k]['sport'] = $v[9];
                        $city[$k]['other_score'] = $v[10];
                        $city[$k]['pro_score'] = $v[11];
                        $i++;  
                    }  
                    try
                    {
                        $success=Db::table('user')->insertAll($city); //批量插入数据  
                        $error=$i-$success;  
                        echo "总{$i}条，成功{$success}条，失败{$error}条。";  
                   // Db::name('t_station')->insertAll($city); //批量插入数据  
                    }
                   catch(Exception $e)
                   {
                        die("插入了已存在的数据");
                   }
                }
               
            }else{  
                // 上传失败获取错误信息  
                echo $file->getError();  
            }  
        }
        else
        {
           echo "上传文件为空";
        }
  
    }  

}
